﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ExcelDemo.Helpers
{
    public class EpplusHelper
    {

        public const string ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        /// <summary>
        /// 插入单元格数据
        /// </summary>
        /// <param name="workSheet">ExcelWorksheet 对象</param>
        /// <param name="rowIndex">行</param>
        /// <param name="colIndex">列</param>
        /// <param name="value">值</param>
        /// <param name="border">是否显示边框</param>
        /// <param name="mergeColumns">合并的几列</param>
        /// <param name="mergeRow">合并的几行</param>
        public static void AddCells(ExcelWorksheet workSheet, int rowIndex, int colIndex, object value, bool border = true, int mergeColumns = 0, int mergeRow = 0)
        {
            switch (value.GetType().ToString())
            {
                case "System.String"://字符串类型
                    workSheet.Cells[rowIndex, colIndex].Value = value;
                    break;

                case "System.DateTime"://日期类型
                    workSheet.Cells[rowIndex, colIndex].Style.Numberformat.Format = "yyyy-mm-d h:mm:ss";
                    workSheet.Cells[rowIndex, colIndex].Value = value;
                    break;

                case "System.Boolean"://布尔型
                    workSheet.Cells[rowIndex, colIndex].Value = (bool)value ? "是" : "否";
                    break;

                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    workSheet.Cells[rowIndex, colIndex].Style.Numberformat.Format = "#,##0";
                    workSheet.Cells[rowIndex, colIndex].Value = value;
                    break;

                case "System.Decimal"://浮点型
                case "System.Double":
                    workSheet.Cells[rowIndex, colIndex].Style.Numberformat.Format = "#,##0.00";
                    workSheet.Cells[rowIndex, colIndex].Value = value;
                    break;

                case "System.DBNull"://空值处理
                    workSheet.Cells[rowIndex, colIndex].Value = "";
                    break;

                default:
                    workSheet.Cells[rowIndex, colIndex].Value = "";
                    break;
            }

            //合并单元格
            if (mergeColumns > 0 || mergeRow > 0)
            {
                workSheet.Cells[rowIndex, colIndex, rowIndex + mergeRow, colIndex + mergeColumns].Merge = true;
                if (border)
                {
                    workSheet.Cells[rowIndex, colIndex, rowIndex + mergeRow, colIndex + mergeColumns].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowIndex, colIndex, rowIndex + mergeRow, colIndex + mergeColumns].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowIndex, colIndex, rowIndex + mergeRow, colIndex + mergeColumns].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    workSheet.Cells[rowIndex, colIndex, rowIndex + mergeRow, colIndex + mergeColumns].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                }
            }
            else if (border)
            {
                workSheet.Cells[rowIndex, colIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[rowIndex, colIndex].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[rowIndex, colIndex].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                workSheet.Cells[rowIndex, colIndex].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            }
        }

        /// <summary>
        /// 自动宽度
        /// </summary>
        /// <param name="workSheet"></param>
        public static void AutoWidth(ExcelWorksheet workSheet)
        {
            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
        }

        /// <summary>
        /// 添加下拉框验证，从第二行开始添加
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex">开始行，1开始</param>
        /// <param name="colIndex">列，1开始</param>
        /// <param name="listOfValues">下拉框值</param>
        public static void AddSelectCol(ExcelWorksheet workSheet, int rowIndex, int colIndex, string[] listOfValues)
        {
            // 下坐标
            string[] colAddress = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", };
            var colStr = colAddress[colIndex - 1];
            //区域范围，xlsx最大行1048575
            var validation = workSheet.DataValidations.AddListValidation($"{colStr}{rowIndex}:{colStr}1048575");
            validation.ShowErrorMessage = true;
            validation.ErrorStyle = OfficeOpenXml.DataValidation.ExcelDataValidationWarningStyle.warning;
            validation.ErrorTitle = "请按右侧下拉箭头选择";
            validation.Error = "无效数据";
            //添加下拉框的值（字数总长度超过256会失效）
            foreach (var item in listOfValues)
            {
                validation.Formula.Values.Add(item);
            }
        }
    }
}
